/*
 * Copyright (c) 2017,安徽皖通科技股份有限公司 All rights reserved.
 */

package com.wttech.dzzf.process.dao;

import com.wtkj.dao.impl.ListResultSetExtractor;
import com.wtkj.vo.dzzf.base.kfwd.CardInfo;
import com.wtkj.vo.dzzf.base.kfwd.FundAccount;
import com.wtkj.vo.dzzf.base.kfwd.OBU;
import com.wtkj.vo.dzzf.base.kfwd.VehicleInfo;
import com.wtkj.vo.dzzf.base.kfzx.ObuGoods;
import org.apache.commons.collections.CollectionUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * 数据治理DAO实现
 *
 * @author MaShibo
 * @date 2017/12/7.
 */
@Repository
public class DataGovernDao {

	@Resource(name = "jdbcTemplate")
	private JdbcTemplate testJdbcTemplate;

	@Resource
	private JdbcTemplate dzzfJdbcTemplate;


	public int queryCardInfoNum() {
		final String sql = "SELECT COUNT(1) FROM AC_CARDINFO A WHERE A.ECARDSTATUS IN (1, 2, 3)";
		return getJdbcTemplate().queryForObject(sql, Integer.class);
	}

	public int queryFundAccountNum() {
		final String sql =
				"SELECT COUNT(1) " +
						"  FROM AC_FUNDACCOUNT A " +
						" WHERE A.ACCOUNTSTATUS = 1 " +
						"   AND EXISTS (SELECT 1 " +
						"          FROM AC_ACCOUNT T " +
						"          JOIN AC_CARDINFO C " +
						"            ON T.ACCOUNTID = C.ACCOUNTID " +
						"         WHERE T.FUNDACCOUNTID = A.FUNDACCOUNTID)";
		return getJdbcTemplate().queryForObject(sql, Integer.class);
	}

	@SuppressWarnings("unchecked")
	public List<FundAccount> queryFundAccountsByRow(int startRow, int endRow) {
		final String sql =
				"SELECT * " +
						"  FROM (SELECT A.*, ROWNUM AS RM " +
						"          FROM AC_FUNDACCOUNT A " +
						"         WHERE A.ACCOUNTSTATUS = 1 " +
						"           AND EXISTS (SELECT 1 " +
						"                  FROM AC_ACCOUNT T " +
						"                  JOIN AC_CARDINFO C " +
						"                    ON T.ACCOUNTID = C.ACCOUNTID " +
						"                 WHERE T.FUNDACCOUNTID = A.FUNDACCOUNTID)) " +
						" WHERE RM >= ? " +
						"   AND RM <= ?";
		return (List<FundAccount>) getJdbcTemplate().query(sql, new ListResultSetExtractor(new FundAccount()), startRow, endRow);
	}

	@SuppressWarnings("unchecked")
	public List<CardInfo> queryCardInfosByFundAccountId(String fundAccountId) {
		final String sql =
				"SELECT * " +
						"  FROM AC_CARDINFO A " +
						"  JOIN AC_ACCOUNT T " +
						"    ON A.ACCOUNTID = T.ACCOUNTID " +
						" WHERE T.FUNDACCOUNTID = ?";
		return (List<CardInfo>) getJdbcTemplate().query(sql, new ListResultSetExtractor(new CardInfo()), fundAccountId);
	}

	@SuppressWarnings("unchecked")
	public List<CardInfo> queryCardinfosByRow(int startRow, int endRow) {
		final String sql =
				"SELECT * " +
						"  FROM (SELECT A.*, ROWNUM RM FROM AC_CARDINFO A) " +
						" WHERE RM >= ? " +
						"   AND RM <= ?";
		return (List<CardInfo>) getJdbcTemplate().query(sql, new ListResultSetExtractor(new CardInfo()), startRow, endRow);
	}

	@SuppressWarnings("unchecked")
	public CardInfo queryDzzfCardInfoById(String cardInfoId) {
		final String sql = "SELECT * FROM AC_CARDINFO A WHERE A.CARDINFOID=? AND A.ECARDSTATUS IN (1,2,3)";
		List<CardInfo> cardInfos = (List<CardInfo>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new CardInfo()), cardInfoId);
		return CollectionUtils.isEmpty(cardInfos) ? null : cardInfos.get(0);
	}

	/**
	 * 更新卡片相关信息
	 *
	 * @param cardInfoId 卡片记录ID
	 * @param params     待更新的参数
	 * @return 更新数量
	 */
	public int updateCardAboutInfos(String cardInfoId, Map<String, Object> params) {
		StringBuilder sql = new StringBuilder("UPDATE AC_CARDINFO SET ");
		List<Object> paramList = new ArrayList<>(params.size());
		params.forEach((k, v) -> {
			sql.append(k).append(" = ").append(" ? ").append(" ,");
			paramList.add(v);
		});
		sql.deleteCharAt(sql.length() - 1);
		sql.append(" WHERE CARDINFOID = ? ");
		paramList.add(cardInfoId);
		return dzzfJdbcTemplate.update(sql.toString(), paramList.toArray());
	}

	public int updateVehInfoNulls(List<String> params, String vehicleInfoId) {
		StringBuilder sql = new StringBuilder("UPDATE AC_VEHICLEINFO SET ");
		params.forEach(param -> sql.append(param).append(" = ").append(" NULL").append(" ,"));
		sql.deleteCharAt(sql.length() - 1);
		sql.append(" WHERE VEHICLEINFOID = ? ");
		return dzzfJdbcTemplate.update(sql.toString(), vehicleInfoId);
	}

	@SuppressWarnings("unchecked")
	public List<OBU> queryObuInfosByVeh(String vehPlate, Integer vehColor) {
		final String sql =
				"SELECT A.* " +
						"  FROM AC_OBU A " +
						" WHERE A.STATE = 1 " +
						"   AND A.VEHICLEPLATE = ? " +
						"   AND A.VEHICLECOLOR = ?";
		return (List<OBU>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new OBU()), vehPlate, vehColor);
	}

	@SuppressWarnings("unchecked")
	public ObuGoods queryObuGoodsInfoByNo(String obuNo) {
		final String sql = "SELECT A.* FROM KC_OBUGOODS A WHERE A.OBUNO = ? ";
		List<ObuGoods> obuGoodsList = (List<ObuGoods>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new ObuGoods()), obuNo);
		return CollectionUtils.isEmpty(obuGoodsList) ? null : obuGoodsList.get(0);
	}

	@SuppressWarnings("unchecked")
	public VehicleInfo queryVehInfosByVeh(String vehPlate, Integer vehColor) {
		final String sql =
				"SELECT * " +
						"  FROM (SELECT A.*, ROWNUM AS RM " +
						"          FROM AC_VEHICLEINFO A " +
						"         WHERE A.VEHPLATE = ? " +
						"           AND A.VEHCOLOR = ?) " +
						" WHERE RM = 1";
		List<VehicleInfo> vehicleInfos = (List<VehicleInfo>) dzzfJdbcTemplate.query(sql, new ListResultSetExtractor(new VehicleInfo()), vehPlate, vehColor);
		return CollectionUtils.isEmpty(vehicleInfos) ? null : vehicleInfos.get(0);
	}

	private JdbcTemplate getJdbcTemplate() {
		return dzzfJdbcTemplate;
	}
}
